import pandas as pd
import sqlite3
import os
import warnings

# 忽略警告信息
warnings.filterwarnings('ignore')

def excel_to_sql(excel_file, table_name='excel_data', db_name='output.db'):
    """
    将Excel文件转换为SQLite数据库
    
    参数:
    excel_file: Excel文件路径
    table_name: 数据库表名
    db_name: SQLite数据库文件名
    """
    try:
        # 读取Excel文件
        df = pd.read_excel(excel_file)
    except Exception as e:
        print(f"读取Excel文件失败: {e}")
        return None
    
    # 连接到SQLite数据库
    conn = sqlite3.connect(db_name)
    
    # 将DataFrame写入数据库
    df.to_sql(table_name, conn, if_exists='replace', index=False)
    
    # 获取表结构信息
    cursor = conn.cursor()
    cursor.execute(f"PRAGMA table_info({table_name})")
    columns = cursor.fetchall()
    
    # 生成CREATE TABLE语句
    create_table_sql = f"CREATE TABLE {table_name} (\n"
    for i, column in enumerate(columns):
        col_name = column[1]
        col_type = column[2]
        # 简化数据类型
        if 'INT' in col_type.upper():
            sql_type = 'INTEGER'
        elif 'CHAR' in col_type.upper() or 'TEXT' in col_type.upper():
            sql_type = 'TEXT'
        elif 'REAL' in col_type.upper() or 'FLOAT' in col_type.upper() or 'DOUBLE' in col_type.upper():
            sql_type = 'REAL'
        else:
            sql_type = 'TEXT'
        
        create_table_sql += f"    {col_name} {sql_type}"
        if i < len(columns) - 1:
            create_table_sql += ","
        create_table_sql += "\n"
    create_table_sql += ");\n\n"
    
    # 生成INSERT语句
    insert_sql = ""
    for _, row in df.iterrows():
        insert_sql += f"INSERT INTO {table_name} VALUES ("
        for i, value in enumerate(row):
            if pd.isna(value):
                insert_sql += "NULL"
            elif isinstance(value, str):
                # 转义单引号
                escaped_value = str(value).replace("'", "''")
                insert_sql += f"'{escaped_value}'"
            else:
                insert_sql += str(value)
            
            if i < len(row) - 1:
                insert_sql += ", "
        insert_sql += ");\n"
    
    # 写入SQL文件
    sql_file = f"{os.path.splitext(excel_file)[0]}.sql"
    with open(sql_file, 'w', encoding='utf-8') as f:
        f.write(create_table_sql)
        f.write(insert_sql)
    
    conn.close()
    
    print(f"Excel文件已转换为SQL脚本: {sql_file}")
    print(f"SQLite数据库已创建: {db_name}")
    return sql_file

def excel_to_mysql_script(excel_file, table_name='excel_data'):
    """
    将Excel文件转换为MySQL脚本
    
    参数:
    excel_file: Excel文件路径
    table_name: 数据库表名
    """
    try:
        # 读取Excel文件
        df = pd.read_excel(excel_file)
    except Exception as e:
        print(f"读取Excel文件失败: {e}")
        return None
    
    # 生成CREATE TABLE语句
    create_table_sql = f"CREATE TABLE `{table_name}` (\n"
    create_table_sql += "  `id` int(11) NOT NULL AUTO_INCREMENT,\n"
    
    # 根据列数据类型生成SQL语句
    for col_name in df.columns:
        # 尝试确定列的数据类型
        sample_data = df[col_name].dropna().head(10)
        
        if sample_data.dtype == 'object':
            # 检查是否为日期
            try:
                pd.to_datetime(sample_data, format='mixed')
                col_type = 'DATETIME'
            except:
                # 检查是否为数字
                try:
                    pd.to_numeric(sample_data)
                    col_type = 'DECIMAL(10,2)'
                except:
                    col_type = 'VARCHAR(255)'
        elif sample_data.dtype == 'int64':
            col_type = 'INT'
        elif sample_data.dtype == 'float64':
            col_type = 'DECIMAL(10,2)'
        else:
            col_type = 'VARCHAR(255)'
        
        create_table_sql += f"  `{col_name}` {col_type} DEFAULT NULL,\n"
    
    create_table_sql += "  PRIMARY KEY (`id`)\n"
    create_table_sql += ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\n\n"
    
    # 生成INSERT语句
    insert_sql = ""
    for _, row in df.iterrows():
        insert_sql += f"INSERT INTO `{table_name}` ("
        col_names = [f"`{col}`" for col in df.columns]
        insert_sql += ", ".join(col_names)
        insert_sql += ") VALUES ("
        
        values = []
        for value in row:
            if pd.isna(value):
                values.append("NULL")
            elif isinstance(value, str):
                # 转义单引号
                escaped_value = str(value).replace("'", "\\'")
                values.append(f"'{escaped_value}'")
            elif isinstance(value, (int, float)):
                values.append(str(value))
            else:
                escaped_value = str(value).replace("'", "\\'")
                values.append(f"'{escaped_value}'")
        
        insert_sql += ", ".join(values)
        insert_sql += ");\n"
    
    # 写入SQL文件
    sql_file = f"{os.path.splitext(excel_file)[0]}_mysql.sql"
    with open(sql_file, 'w', encoding='utf-8') as f:
        f.write("SET NAMES utf8mb4;\n")
        f.write("SET FOREIGN_KEY_CHECKS = 0;\n\n")
        f.write(create_table_sql)
        f.write(insert_sql)
        f.write("\nSET FOREIGN_KEY_CHECKS = 1;\n")
    
    print(f"MySQL脚本已生成: {sql_file}")
    return sql_file

if __name__ == "__main__":
    # 使用示例
    excel_file = "../W020250627301230143042.xls"
    
    # 转换为SQLite脚本
    try:
        excel_to_sql(excel_file, "university_data", "university.db")
    except Exception as e:
        print(f"转换为SQLite时出错: {e}")
    
    # 转换为MySQL脚本
    try:
        excel_to_mysql_script(excel_file, "university_data")
    except Exception as e:
        print(f"转换为MySQL脚本时出错: {e}")